package dao.impl;

import bean.vo.*;
import commons.dbHelper.DBHelper;
import dao.EchartsDao;

import java.util.List;

/**
 * dao.impl
 * User: 邱
 * Date: 2024/12/16 18:42
 * Description:
 * Version: V1.0
 */
public class EchartsDaoImpl implements EchartsDao {
    DBHelper dbHelper = new DBHelper();

    /**
     * 查询缴费情况
     * @return
     */
    @Override
    public List<MonthDataVo> findChargeList(List<String> date) {
        String sql = "SELECT SUM(IF(charge_year_month = ?,charge_amount,0)) AS january, SUM(IF(charge_year_month = ?,charge_amount,0)) AS february, SUM(IF(charge_year_month = ?,charge_amount,0)) AS march, SUM(IF(charge_year_month = ?,charge_amount,0)) AS april, SUM(IF(charge_year_month = ?,charge_amount,0)) AS may, SUM(IF(charge_year_month = ?,charge_amount,0)) AS june, SUM(IF(charge_year_month = ?,charge_amount,0)) AS july, SUM(IF(charge_year_month = ?,charge_amount,0)) AS august, SUM(IF(charge_year_month = ?,charge_amount,0)) AS september, SUM(IF(charge_year_month = ?,charge_amount,0)) AS october, SUM(IF(charge_year_month = ?,charge_amount,0)) AS november, SUM(IF(charge_year_month = ?,charge_amount,0)) AS december, CASE WHEN is_dunned IN (0, 2) THEN 0 ELSE is_dunned END AS is_dunned FROM charge_record WHERE charge_year_month LIKE concat( ?, '%') GROUP BY CASE WHEN is_dunned IN (0, 2) THEN 0 ELSE is_dunned END";
        return dbHelper.getBeanList(MonthDataVo.class, sql, date.toArray());
    }

    /**
     * 查询访客情况
     * @return
     */
    @Override
    public MonthDataVo findVisitorList() {
        String sql = "SELECT SUM(CASE WHEN MONTH(visit_time) = 1 THEN 1 ELSE 0 END) AS january, SUM(CASE WHEN MONTH(visit_time) = 2 THEN 1 ELSE 0 END) AS february, SUM(CASE WHEN MONTH(visit_time) = 3 THEN 1 ELSE 0 END) AS march, SUM(CASE WHEN MONTH(visit_time) = 4 THEN 1 ELSE 0 END) AS april, SUM(CASE WHEN MONTH(visit_time) = 5 THEN 1 ELSE 0 END) AS may, SUM(CASE WHEN MONTH(visit_time) = 6 THEN 1 ELSE 0 END) AS june, SUM(CASE WHEN MONTH(visit_time) = 7 THEN 1 ELSE 0 END) AS july, SUM(CASE WHEN MONTH(visit_time) = 8 THEN 1 ELSE 0 END) AS august, SUM(CASE WHEN MONTH(visit_time) = 9 THEN 1 ELSE 0 END) AS september, SUM(CASE WHEN MONTH(visit_time) = 10 THEN 1 ELSE 0 END) AS october, SUM(CASE WHEN MONTH(visit_time) = 11 THEN 1 ELSE 0 END) AS november, SUM(CASE WHEN MONTH(visit_time) = 12 THEN 1 ELSE 0 END) AS december FROM visitor WHERE YEAR(visit_time) = YEAR(NOW())";
        return dbHelper.getBean(MonthDataVo.class, sql, null);
    }

    /***
     * 查询投诉情况
     * @return
     */
    @Override
    public MonthDataVo findComplaintList() {
        String sql = "SELECT SUM(CASE WHEN MONTH(complaint_time) = 1 THEN 1 ELSE 0 END) AS january, SUM(CASE WHEN MONTH(complaint_time) = 2 THEN 1 ELSE 0 END) AS february, SUM(CASE WHEN MONTH(complaint_time) = 3 THEN 1 ELSE 0 END) AS march, SUM(CASE WHEN MONTH(complaint_time) = 4 THEN 1 ELSE 0 END) AS april, SUM(CASE WHEN MONTH(complaint_time) = 5 THEN 1 ELSE 0 END) AS may, SUM(CASE WHEN MONTH(complaint_time) = 6 THEN 1 ELSE 0 END) AS june, SUM(CASE WHEN MONTH(complaint_time) = 7 THEN 1 ELSE 0 END) AS july, SUM(CASE WHEN MONTH(complaint_time) = 8 THEN 1 ELSE 0 END) AS august, SUM(CASE WHEN MONTH(complaint_time) = 9 THEN 1 ELSE 0 END) AS september, SUM(CASE WHEN MONTH(complaint_time) = 10 THEN 1 ELSE 0 END) AS october, SUM(CASE WHEN MONTH(complaint_time) = 11 THEN 1 ELSE 0 END) AS november, SUM(CASE WHEN MONTH(complaint_time) = 12 THEN 1 ELSE 0 END) AS december FROM complaint_record WHERE YEAR(complaint_time) = YEAR(NOW())";
        return dbHelper.getBean(MonthDataVo.class, sql, null);
    }

    /**
     * 查询售卖情况
     * @return
     */
    @Override
    public MonthDataVo findSaleGoodsList() {
        String sql = "SELECT SUM(CASE WHEN MONTH(order_time) = 1 THEN number ELSE 0 END) AS january, SUM(CASE WHEN MONTH(order_time) = 2 THEN number ELSE 0 END) AS february, SUM(CASE WHEN MONTH(order_time) = 3 THEN number ELSE 0 END) AS march, SUM(CASE WHEN MONTH(order_time) = 4 THEN number ELSE 0 END) AS april, SUM(CASE WHEN MONTH(order_time) = 5 THEN number ELSE 0 END) AS may, SUM(CASE WHEN MONTH(order_time) = 6 THEN number ELSE 0 END) AS june, SUM(CASE WHEN MONTH(order_time) = 7 THEN number ELSE 0 END) AS july, SUM(CASE WHEN MONTH(order_time) = 8 THEN number ELSE 0 END) AS august, SUM(CASE WHEN MONTH(order_time) = 9 THEN number ELSE 0 END) AS september, SUM(CASE WHEN MONTH(order_time) = 10 THEN number ELSE 0 END) AS october, SUM(CASE WHEN MONTH(order_time) = 11 THEN number ELSE 0 END) AS november, SUM(CASE WHEN MONTH(order_time) = 12 THEN number ELSE 0 END) AS december FROM (SELECT opr.relation_id, opr.number, o.order_time FROM order_product_relation opr LEFT JOIN orders o ON opr.order_id = o.order_id ) AS real_1 WHERE YEAR(order_time) = YEAR(NOW())";
        return dbHelper.getBean(MonthDataVo.class, sql, null);
    }

    /**
     * 查询费用类型及其对应费用
     * @return
     */
    @Override
    public List<PaymentVo> findChargeRecord(String date) {
        String sql = "SELECT e.type_name, SUM(c.charge_amount) AS payment, c.is_dunned FROM charge_record c JOIN expense_type e ON c.type_id = e.type_id WHERE c.charge_year_month LIKE concat( ?, '%') GROUP BY e.type_name, (CASE WHEN c.is_dunned IN (0, 2) THEN 0 ELSE c.is_dunned END)";
        return dbHelper.getBeanList(PaymentVo.class, sql, date);
    }

    /**
     * 查询维修项目及其对应次数
     * @return
     */
    @Override
    public List<MaintenanceVo> findMaintenance() {
        String sql = "SELECT (CASE WHEN maintenance_item = \"\" THEN \"空维修项目\" ELSE maintenance_item END) AS maintenance_item, COUNT(maintenance_record_id) AS maintenance_num FROM equipment_maintenance GROUP BY maintenance_item";
        return dbHelper.getBeanList(MaintenanceVo.class, sql, null);
    }

    /**
     * 查询医疗预约项目及其对应预约数
     * @return
     */
    @Override
    public List<AppointmentVo> findMedicalList() {
        String sql = "SELECT medical_item, COUNT(medical_item) AS value FROM medical_appointment GROUP BY medical_item";
        return dbHelper.getBeanList(AppointmentVo.class, sql, null);
    }

    /**
     * 查询访客人数
     * @return
     */
    @Override
    public VisitorNumVo findVisitorNumber() {
        String sql = "SELECT COUNT(visitor_id) AS visitor_num FROM visitor";
        return dbHelper.getBean(VisitorNumVo.class, sql, null);
    }

    @Override
    public DwellerNumVo findAllOwner() {
        String sql = "SELECT COUNT(user_id) AS owner_num FROM user WHERE is_owner = 1";
        return dbHelper.getBean(DwellerNumVo.class, sql, null);
    }

    @Override
    public DwellerNumVo findAllDweller() {
        String sql = "SELECT COUNT(user_id) AS dweller_num FROM user";
        return dbHelper.getBean(DwellerNumVo.class, sql, null);
    }

    @Override
    public PaymentVo findPayment() {
        String sql = "SELECT SUM(IF(is_dunned = 1,charge_amount,0)) AS has_payment, SUM(IF(is_dunned IN (0, 2),charge_amount,0)) AS not_payment FROM charge_record";
        return dbHelper.getBean(PaymentVo.class, sql, null);
    }

    /***
     * 查询售卖商品数
     * @return
     */
    @Override
    public OrderInfoVo findSaleGoodsNum() {
        String sql = "SELECT SUM(number) AS sale_goods_num FROM order_product_relation";
        return dbHelper.getBean(OrderInfoVo.class, sql, null);
    }

    /**
     * 查询投诉数
     * @return
     */
    @Override
    public DwellerNumVo findComplaintNum() {
        String sql = "SELECT COUNT(complaint_record_id) AS complaint_num FROM complaint_record";
        return dbHelper.getBean(DwellerNumVo.class, sql, null);
    }

}
